/*
 * Copyright 2013 Agustina Martinez-Garcia

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */
package uk.ljmu.qudexapps.manager.utilities;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FilenameFilter;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.igfay.jfig.JFig;
import org.igfay.jfig.JFigException;
import org.springframework.web.multipart.MultipartFile;

import uk.ljmu.qudexapps.manager.exceptions.ExcelManagementException;

import eu.medsea.mimeutil.MimeType;
import eu.medsea.mimeutil.MimeUtil;

/**
 * This class implements all the auxiliary functions to manage Excel spreadsheets and it is used globally within the 
 * web application
 * @author agustina
 *
 */
public class ExcelManagement {
    final static int BUFFER = 2048;
    private List<String> fileList;
    private static Logger log = Logger.getLogger(ExcelManagement.class);
    private String fileSeparator = System.getProperty("file.separator");

    public void setListOfFiles(List<String> listOfFiles) {
	this.fileList = listOfFiles;
    }

    public List<String> getListOfFiles() {
	return fileList;
    }

    public ExcelManagement() {
	this.fileList = new ArrayList<String>();
	//this.fileSeparator = System.getProperty("file.separator");
    }

    /**
     * 
     * @param spreadsheetFile
     * @param serverPath
     * @param temporaryDir
     * @param tempDirName
     * @return
     * @throws IOException
     * @throws ExcelManagementException
     */
    public String fillExcelFile(MultipartFile spreadsheetFile,
	    String serverPath, String tempDirName, String temporaryDir)
    throws IOException, ExcelManagementException {

	// create a new file
	InputStream inData = null;
	FileOutputStream fileOut = null;

	try {
	    inData = spreadsheetFile.getInputStream();

	    HSSFWorkbook wb;

	    wb = new HSSFWorkbook(inData);

	    HSSFSheet sheet1 = wb.getSheetAt(0);
	    List<Integer> dsIndexesList = findAllDatastreamColumnExcel(sheet1);

	    boolean results = autoFillSpreadsheet(sheet1, dsIndexesList,
		    serverPath, tempDirName);
	    if (!results) {
		log.error("ZIP uploaded but file column in spreadsheet is not present. Check spreadsheet.");
		throw new ExcelManagementException(
		    "ZIP uploaded but file column in spreadsheet is not present. Check spreadsheet.");
	    }
	    String newFileName = (temporaryDir.endsWith(fileSeparator)) ? temporaryDir
		    + spreadsheetFile.getOriginalFilename()
		    : temporaryDir + fileSeparator
		    + spreadsheetFile.getOriginalFilename();
		    fileOut = new FileOutputStream(newFileName);
		    wb.write(fileOut);
		    return newFileName;
	} catch (IOException e) {
	    log.fatal(e.getMessage());
	    throw new ExcelManagementException(
	    "Error while reading uploaded spreadsheet file, check logs...");
	} catch (JFigException e) {
	    log.fatal(e.getMessage());
	    throw new ExcelManagementException(
	    "Error while reading configuration file, check logs...");
	} finally {
	    if (fileOut != null)
		fileOut.close();
	}
    }

    /**
     * Auto-fills an spreadsheet with the data-stream contents file paths and
     * dc:format field
     * 
     * @param sheet
     * @param idColumnIndex
     * @param dsColumnIndex
     * @param directoryPath
     * 
     * @throws JFigException
     * @throws Exception
     */
    public void autoFillSpreadsheet(HSSFSheet sheet, int idColumnIndex,
	    int dsColumnIndex, String directoryPath, String temporaryDir)
    throws ExcelManagementException, JFigException {
	File path;

	// We use the session default temporary folder, which gets cleaned when
	// the session expires...
	// Path in the form of /path-to-session-temporary-folder
	path = new File((directoryPath.endsWith(fileSeparator)) ? directoryPath
		+ temporaryDir : directoryPath + fileSeparator + temporaryDir);
	// Process the temporary directory to find the uploaded files
	processDirectory(path);

	if (fileList == null) {
	    throw new ExcelManagementException("Files directory empty...");
	}
	if (fileList.size() == 0) {
	    throw new ExcelManagementException("No files found...");

	}
	// FILL spreadsheet rows
	Iterator<Row> it = sheet.rowIterator();
	;
	int indexFormatElement = findFormatElementExcel(sheet,
		Constants.DC_FORMAT_HEADER);

	while (it.hasNext()) {
	    HSSFRow r = (HSSFRow) it.next();
	    if (indexFormatElement == -1) {
		// Create dc:format column
		HSSFCell c1 = r.createCell((int) r.getLastCellNum());
		c1.setCellValue(Constants.DC_FORMAT_HEADER);
		indexFormatElement = c1.getColumnIndex();
	    }
	    if (r.getRowNum() == 0) {
		continue;
	    }
	    HSSFCell cell;
	    String dsFileName;
	    cell = r.getCell(idColumnIndex);
	    dsFileName = findFileInDirectory(cell.getRichStringCellValue()
		    .getString());

	    if (dsFileName != "") {
		File file = new File(dsFileName);
		cell = r.createCell(dsColumnIndex, HSSFCell.CELL_TYPE_STRING);
		String completeUrl = "";
		// The value comes from the configuration file in the paths
		// section
		completeUrl = JFig.getInstance().getValue("fedoraServer",
		"fileServletUrl");
		// +1 to jump '/'
		String partialPath = "";
		try {
		    partialPath = dsFileName.substring(dsFileName
			    .lastIndexOf(fileSeparator) + 1, dsFileName
			    .length());
		} catch (IndexOutOfBoundsException e) {
		    throw new ExcelManagementException(
		    "Error when finding Temporary directory, check log file");
		}
		HSSFCell formatCell = r.getCell(indexFormatElement);
		if (formatCell == null) {
		    formatCell = r.createCell(indexFormatElement,
			    HSSFCell.CELL_TYPE_STRING);
		    MimeUtil
		    .registerMimeDetector("eu.medsea.mimeutil.detector.MagicMimeMimeDetector");
		    Collection<?> mimeTypes = MimeUtil.getMimeTypes(file);
		    if (!mimeTypes.isEmpty()) {
			MimeType mtype = (MimeType) mimeTypes.iterator().next();
			formatCell.setCellValue(mtype.toString());
		    } else
			formatCell.setCellValue("");
		} else if (formatCell.getStringCellValue().equals("")) {
		    MimeUtil
		    .registerMimeDetector("eu.medsea.mimeutil.detector.MagicMimeMimeDetector");
		    Collection<?> mimeTypes = MimeUtil.getMimeTypes(file);
		    if (!mimeTypes.isEmpty()) {
			MimeType mtype = (MimeType) mimeTypes.iterator().next();
			formatCell.setCellValue(mtype.toString());
		    } else
			formatCell.setCellValue("");
		}
		cell.setCellValue(completeUrl + "?fileName=" + partialPath
			+ "&type=" + formatCell.getStringCellValue());
		log.info(completeUrl + "?fileName=" + partialPath + "&type="
			+ r.getCell(indexFormatElement).getStringCellValue());
	    }
	}
    }

    /**
     * Autofills an spreadsheet with the datastream contents file paths and
     * dc:format field
     * 
     * @param sheet
     *            spreadsheet sheet object
     * @param idColumnIndex
     *            index of the PID column in the spreadsheet
     * @param dsIndexesList
     *            list of indexes of managed datastreams columns found in the
     *            spreadsheet
     * @param directoryPath
     *            string with the web-path to the temporary directory
     * @throws JFigException
     * @throws IOException 
     * @throws Exception
     */
    public boolean autoFillSpreadsheet(HSSFSheet sheet,
	    List<Integer> dsIndexesList, String directoryPath,
	    String temporaryDir)
    throws ExcelManagementException, JFigException, IOException {
	StringBuilder sb = new StringBuilder();
	
	if (dsIndexesList.size() < 1 || (dsIndexesList.size() == 1 && dsIndexesList.get(0) == null))
	    return false;
	if (directoryPath.endsWith(fileSeparator)) {
	    sb.append(directoryPath);
	    sb.append(temporaryDir);
	} else {
	    sb.append(directoryPath);
	    sb.append(fileSeparator);
	    sb.append(temporaryDir);
	}
	// FILL spreadsheet rows
	Iterator<Row> it = sheet.rowIterator();
	;
	int indexFormatElement = findFormatElementExcel(sheet,
		Constants.DC_FORMAT_HEADER);
	//try {
	    while (it.hasNext()) {
		HSSFRow r = (HSSFRow) it.next();
		if (indexFormatElement == -1) {
		    // Create dc:format column
		    HSSFCell c1 = r.createCell((int) r.getLastCellNum());
		    c1.setCellValue(Constants.DC_FORMAT_HEADER);
		    indexFormatElement = c1.getColumnIndex();
		}
		if (r.getRowNum() == 0) {
		    continue;
		}
		for (int dsColumnIndex : dsIndexesList) {
		    HSSFCell cell;
		    String dsFileName;
		    
		    cell = r.getCell(dsColumnIndex);
		    if (cell != null && !cell.getStringCellValue().equals("")) {
			int deleteIndex = sb.toString().length();
			sb.append(fileSeparator);
			sb.append(cell.getStringCellValue());
			dsFileName = sb.toString();
			sb.delete(deleteIndex, sb.toString().length());
		    } else
			dsFileName = "";
		    if (dsFileName != "") {
			File file = new File(dsFileName);
			cell = r.createCell(dsColumnIndex,
				HSSFCell.CELL_TYPE_STRING);
			String completeUrl = "";
			// The value comes from the configuration file in the paths
			// section
			completeUrl = JFig.getInstance().getValue("fedoraServer",
			"fileServletUrl");
			// +1 to jump '/'
			String partialPath = "";
			try {
			    partialPath = dsFileName.substring(dsFileName
				    .lastIndexOf(fileSeparator) + 1, dsFileName
				    .length());
			} catch (IndexOutOfBoundsException e) {
			    throw new ExcelManagementException(
			    "Error when finding Temporary directory, check log file");
			}
			HSSFCell formatCell = r.getCell(indexFormatElement);
			if (formatCell == null
				|| formatCell.getStringCellValue().equals("")) {
			    formatCell = r.createCell(indexFormatElement,
				    HSSFCell.CELL_TYPE_STRING);
			     MimeUtil.registerMimeDetector("eu.medsea.mimeutil.detector.MagicMimeMimeDetector");
			     Collection<?> mimeTypes = MimeUtil.getMimeTypes(file);
			     if (!mimeTypes.isEmpty()) {
				 MimeType mtype = (MimeType)
				 mimeTypes.iterator().next();
				 formatCell.setCellValue(mtype.toString());
			     }
			     else formatCell.setCellValue("");
			   //try {
				//is = new BufferedInputStream(new FileInputStream(
				//	file));
				//String mimeType = URLConnection.guessContentTypeFromStream(is);
				//if (mimeType != null)
				//    formatCell.setCellValue(mimeType.toString());
				//else formatCell.setCellValue("");
				//is.close();
			    //} catch (FileNotFoundException e) {
				//log.error("Error: ", e);
			    //} catch (IOException e) {
				//log.error("Error: ", e);
			    //}

			} else if (formatCell.getStringCellValue().equals("")) {
			    MimeUtil
			    .registerMimeDetector("eu.medsea.mimeutil.detector.MagicMimeMimeDetector");
			    Collection<?> mimeTypes = MimeUtil.getMimeTypes(file);
			    if (!mimeTypes.isEmpty()) {
				MimeType mtype = (MimeType) mimeTypes.iterator()
				.next();
				formatCell.setCellValue(mtype.toString());
			    } else
				formatCell.setCellValue("");
			}
			cell.setCellValue(completeUrl + "?fileName=" + partialPath
				+ "&type=" + formatCell.getStringCellValue());
			log.info(completeUrl
				+ "?fileName="
				+ partialPath
				+ "&type="
				+ r.getCell(indexFormatElement)
				.getStringCellValue());
		    }
		}
	    }
	//} finally {
	//    if (is != null)
	//	is.close();
	//}
	    return true;
    }

    /**
     * 
     * @param sheet
     * @param columnName
     * @return
     */
    private int findFormatElementExcel(HSSFSheet sheet, String columnName) {

	HSSFRow row = sheet.getRow(0);
	Iterator<Cell> iterator = row.cellIterator();
	while (iterator.hasNext()) {
	    Cell cell = iterator.next();
	    String name = String.valueOf(cell.getRichStringCellValue());
	    if ((columnName.equalsIgnoreCase(name)))
		return cell.getColumnIndex();
	}
	// Not found
	return -1;
    }

    /**
     * 
     * @param sheet
     * @param columnName
     * @return
     */
    @SuppressWarnings("unused")
    private int findDatastreamColumnExcel(HSSFSheet sheet) {

	HSSFRow row = sheet.getRow(0);
	Iterator<Cell> iterator = row.cellIterator();
	while (iterator.hasNext()) {
	    Cell cell = iterator.next();
	    String name = String.valueOf(cell.getRichStringCellValue());
	    // Pattern either ':X' or ':M'
	    if (name.contains(Constants.DS_MANAGED))
		return cell.getColumnIndex();
	}
	// Not found
	return -1;
    }

    /**
     * 
     * @param sheet
     * @param columnName
     * @return list of datastream indexes List<Integer>
     */
    private List<Integer> findAllDatastreamColumnExcel(HSSFSheet sheet) {
	List<Integer> indexesList = new ArrayList<Integer>();
	HSSFRow row = sheet.getRow(0);
	Iterator<Cell> iterator = row.cellIterator();
	while (iterator.hasNext()) {
	    Cell cell = iterator.next();
	    String name = String.valueOf(cell.getRichStringCellValue());
	    // Pattern either ':X' or ':M'
	    if (name.contains(Constants.DS_MANAGED))
		indexesList.add(cell.getColumnIndex());
	}
	// Not found
	return indexesList;
    }

    /* GENERAL FUNCTIONS */
    /**
     * 
     * @param namePattern
     * @return
     */
    // TODO AMG(24/01/11) CHECK CHANGES IN THIS FUNCTION
    private String findFileInDirectory(String namePattern) {
	String foundFile = "";
	for (String fileElem : fileList) {
	    if (fileElem.contains(namePattern)) {
		foundFile = fileElem;
		break;
	    }
	}
	return foundFile;
    }

    /**
     * 
     * @param folder
     */
    public void processDirectory(File folder) {
	FilenameFilter filter = new FilenameFilter() {
	    public boolean accept(File dir, String name) {
		return !name.startsWith(".") && !name.startsWith("__");
	    }
	};
	File[] listOfFiles = folder.listFiles(filter);

	for (int i = 0; i < listOfFiles.length; i++) {
	    if (listOfFiles[i].isFile()) {
		fileList.add(listOfFiles[i].getAbsolutePath());
	    } else if (listOfFiles[i].isDirectory()) {
		processDirectory(listOfFiles[i]);
	    }
	}
    }
}
